﻿Imports Microsoft.VisualBasic
Imports System.Data

<System.ComponentModel.DataObjectAttribute(True)> _
Public Class BLL_QueryTrainingCourseGroup

    Public Sub New()
        ' 
        ' TODO: 在此加入建構函式的程式碼 
        ' 
    End Sub

    'SELECT 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.[Select])> _
    Public Function BLL_Select(ByVal UOrganizationID As String, ByVal annual As String, ByVal trainingGroupName As String) As DataTable

        Dim sql As New MyOrganizationDataFilter(True)
        Dim dat As New clsData

        sql.AppendFormat("SELECT distinct tcg.* ")
        sql.AppendFormat(", o.OrganizationName ")
        sql.AppendFormat(",case when tr.groupid is null then '1' else '0' end as IsDelete ")
        sql.AppendFormat("FROM TrainingCourseGroup tcg ")
        sql.AppendFormat("inner join OrganizationView o on tcg.UOrganizationID = o.UOrganizationID ")
        sql.AppendFormat("left join TrainingRegister  tr on tcg.id=tr.groupid ")
        sql.AppendFormat("WHERE (tcg.UOrganizationID='{0}' or '{0}'='') ", UOrganizationID)

        If annual <> "" Then
            sql.AppendFormat("and year(StartDate) = {0} ", annual)
        End If

        If trainingGroupName <> "" Then
            sql.AppendFormat("and tcg.GroupName like '%{0}%' ", trainingGroupName)
        End If

        'MyObj.WriteLog(sql.ToString())
        Return dat.GetData(sql)

    End Function

    'Delete 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Delete)> _
    Public Function BLL_Delete(ByVal original_Id As Integer) As Integer

        Dim sql As New MyStringBuilder
        Dim dat As New clsData

        Using scope As New Transactions.TransactionScope

            sql.AppendFormat("DELETE TrainingCourseGroup WHERE Id={0}; ", original_Id)
            sql.AppendFormat("DELETE TrainingCourseGroupMapping WHERE GroupId={0}; ", original_Id)

        End Using
        dat.AccData(sql)

        Return 1

    End Function

End Class
